Oracle

GeoServer also offers Oracle Spatial support, contributed by Sean Geoghegan, and currently searching for a new maintainer. It includes native support of all OGC filters, connection pooling, and transactional capabilities. Please check the wiki for the latest information on oracle. Latest download available here.

We are no longer dependent on the sdoapi jar, thanks to some nice work done by Refractions Research. There is still one jar file that we can not distribute, the driver for the JDBC connection to Oracle, it should be called ojdbc14.jar. This is targeted at java 1.4, which is required by GeoServer. We have also found that classes12.jar works as well, the main class it needs to have is oracle.jdbc.driver.OracleDriver. If you are installing GeoServer on the same computer that is running Oracle you should be able to find the jar at $ORACLE_HOME/jdbc/lib It is also readily available from the Oracle Technology Network here. Just place the jar in the geoserver/webapps/geoserver/WEB-INF/lib directory of a geoserver binary install. Once the jar is in the directory then Oracle Spatial Database and Oracle Spatial with OCI (thick) connection should be options from the drop down menu when creating a new datastore.

There is a choice in the type of connection that you use to connect to GeoServer. The standard 'Oracle Spatial Database' connection uses a completely portable JDBC library, that runs as a thin client on any computer. If you don't have Oracle installed on your computer, and/or are not an Oracle expert and know exactly how an OCI connection works, then we recommend you use this one.

Often GeoServer will be installed on a computer that has all the OCI (Oracle Call Interface) drivers needed for a thick connection (such as when installed on the same computer as the database). In this case the thick driver is preferable, as it leads to a nice speed increase. It should come up as an option on the new datastore menu, it has a few parameters different from the standard Oracle Spatial connection. Instead of taking a host, port, and instance params it uses an 'alias'. This alias can be found in $ORACLE_HOME/network/admin/tnsnames.ora. Note that if you are using the OCI you must use the classes12.jar or ojdbc14.jar found in your $ORACLE_HOME/jdbc/lib directory.

All Oracle DataStores make use of the following parameters:

Additionally, a standard JDBC connection will make use of the following parameters to specify the machine that Oracle is running on, and the port and instance to connect to.

An OCI connection does not need that information, it uses the 'alias', which contains all the connection information, and indeed is specially configured for it. As we mentioned earlier this alias can be found in

$ORACLE_HOME/network/admin/tnsnames.ora

We have also had some success with leaving the alias as an empty string, it seems to find the appropriate default.

So the param is:

To get your featureTypes working properly the oracle spatial table you are using must have a correct entry in the USER_SDO_GEOM_METADATA table (now works in ALL_SDO_GEOM_METADATA view, so different users than the owner can connect). And whatever table you use must have a primary key declared, it will not work otherwise. We have also seen problems using spatial filters when a spatial index is not defined, so if everything but spatial filters work it may just be that the spatial index is not defined for the table.

We have also recently had a note from users about configuring the FeatureType, if you use the default (XML fragment) then things will sometimes not work properly. Pick the 'String' option, and Oracle should sort out itself what the return type should be. If you have more feedback on this please update the wiki, and we will be working on the problem.

A nice little tool to help out figuring out if your oracle instance can be connected to and what the parameters are is lsnrctl. If you can run it I believe that means that your oracle instance will accept a connection from geoserver. And it is also useful for figuring out the hostname - mine would not accept 'localhost' or '127.0.0.1' (this is with 10g and windows xp), it needed the name of my computer, which was given by the lsnrctl program, with the services command. The 'status' command will also tell if geoserver is connected to your oracle instance. Also note that if you are working directly with the geoserver configuration files the name element in the info.xml file to specify the featureType must be all capitalized, as the JDBC driver does not recognize the table otherwise. This also means that when you request the features from GeoServer you must use upper cases. If specifying attributes in the schema.xml file they must also be all capitalized. Oracle does not use cases, but things seem to work better all around if all upper case letters are used.

Troubleshooting

"When adding an OCI datastore, I would get a message that libocijdbc10.so could not be found in the java.library.path (and another one). The directory which contains that "so" does exist in the LD_LIBRARY_PATH (does Tomcat pick that up?)."

Set the environment for Tomcat appropriate:

export ORACLE_HOME=/opt/oracle/product/10.1.0/Db_1
export
CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc14.jar:$ORACLE_HOME/jdbc/lib/orai18n.jar:$ORACLE_HOME/jdbc/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

The last setting depends on system, we have a SuSe Linux. Sometimes it is LD_PATH or SH_LIB_PATH etc.

"When adding a datastore out of table created with AutoCAD Map, Geoserver complains there are geometry columns not defined in metadata."

The problem lies in the peculiar ways of AutoCAD Map, which writes columns not only to store the entity's geometry, but for ancillary data (such as as blockscale) as well.  These columns though, are not added to Oracle Spatial's metadata, hence the resulting Geoserver embarassment.

The solution (tested with AutoCAD Map 3D 2006, Oracle 10g, Geoserver 1.3.0)  is to filter out those "extra" geometry columns by using a view, and then using the view (and not the underlying table) as a source for the Geoserver feature type.

The steps are the following:

1. Suppos you have created an Oracle table called 'mytable' with AutoCAD Map, and this table contains the entities' geometry in a column called 'geometry'.
2. Create an Oracle view as:

   CREATE VIEW GSmytable AS SELECT geometry, entityid FROM mytable;

3. Add a suitable row for the GSmytable view in USER_SDO_GEOM_METADATA by copying the mytable diminfo and srid columns:

   INSERT	INTO user_sdo_geom_metadata
   	(table_name, column_name, diminfo, srid)
   	SELECT 'GSMYTABLE', column_name, diminfo, srid
   		FROM user_sdo_geom_metadata WHERE table_name = 'MYTABLE';

4. Create the feaure type using the GSmytable view.